package cn.hpclub.server.service;

import cn.hpclub.server.constant.Constant;
import cn.hpclub.server.util.InterfaceUtil;
import cn.hpclub.server.util.excel.ExportUtils;
import com.jfinal.plugin.activerecord.Db;
import com.jfinal.plugin.activerecord.Record;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.io.FileNotFoundException;
import java.io.IOException;
import java.util.List;
import java.util.Map;
import java.util.concurrent.ConcurrentHashMap;

/**
 * Created by wanglin on 2017/2/28.
 * 登录统计
 */
public class LoginCountService {

    private static final Logger logger          = LoggerFactory.getLogger(LoginCountService.class);
    public static final LoginCountService service = new LoginCountService();

//    /**
//     *
//     * @return
//     */
//    public String getSqlHead(){
//        String sql="select lc.city,lc.district,count(lc.id) as loginTimes,count(DISTINCT(lc.login_no)) as loginNumber,o.id as org_id ";
//        return sql;
//    }
//
//    /**
//     * 获取查询语句
//     */
//    public String getSqlExceptSelect(ConcurrentHashMap<String, String> searchInfo){
//        String sql = " From login_count lc left join organization o on lc.channel_id=o.NumCode where 1=1 ";
//
//        for(Map.Entry<String, String> item : searchInfo.entrySet()){
//            sql += buildQuery(item);
//        }
//        sql += " group by lc.area_code";
//        return sql;
//    }

    /**
     *
     * @return
     */
    public String getSqlHead(){
        String sql="select * ";
        return sql;
    }

    /**
     * 获取查询语句
     */
    public String getSqlExceptSelect(ConcurrentHashMap<String, String> searchInfo){
        StringBuffer sql = new StringBuffer();
        sql.append(" from (select lc.city,lc.district,lc.city_code,count(lc.id) as loginTimes,count(DISTINCT(lc.login_no)) as loginNumber,org_id,lc.area_code ");
        sql.append(" From login_count lc where 1=1 and lc.area_code!=0  ");

        for(Map.Entry<String, String> item : searchInfo.entrySet()){
            sql.append(buildQuery(item));
        }
        sql.append(" group by lc.area_code");
        sql.append(" union all ");
        sql.append(" select lc.city,if(ISNULL(lc.district) or lc.district='',lc.city,lc.district) as district,lc.city_code,count(lc.id) as loginTimes,count(DISTINCT(lc.login_no)) as loginNumber,org_id,lc.area_code ");
        sql.append(" from login_count lc where 1=1 and lc.area_code=0  ");
        for(Map.Entry<String, String> item : searchInfo.entrySet()){
            sql.append(buildQuery(item));
        }
        sql.append("  GROUP BY lc.city_code ) as t");
        return sql.toString();
    }


    /**
     *
     * @return
     */
    public String getDetailSqlHead(){
        String sql="select if(ISNULL(lc.district) or lc.district='',lc.city,lc.district) as District,lc.*,DATE_FORMAT(lc.login_time,'%Y-%m-%d %H:%i:%S') as loginTime";
        return sql;
    }

    /**
     * 获取查询语句
     */
    public String getDetailSqlExceptSelect(ConcurrentHashMap<String, String> searchInfo){
        String sql = " from login_count lc where 1=1 ";

        for(Map.Entry<String, String> item : searchInfo.entrySet()){
            sql += buildQueryDetails(item);
        }
        sql += " order by lc.login_time desc";
        return sql;
    }

    /**
     * @describe 获取登录用户统计列表的excel文件
     * @param searchInfo 查询条件参数列表
     * @return SXSSFWorkbook
     */
    public SXSSFWorkbook getExcel(ConcurrentHashMap<String, String> searchInfo){
        SXSSFWorkbook workbook = null;
        String sql = getSqlHead() + getSqlExceptSelect(searchInfo);
        List<Record> seq = Db.find(sql);
        String[] excelHeaders = new String[]{"地市","区县","登录次数","登录人数"};
        ExportUtils exportUtils = new ExportUtils(excelHeaders);
        String[] keys = new String[]{"city","district","loginTimes","loginNumber"};

        try {
            exportUtils.exportExcel(keys,seq,"yyyy-MM-dd HH:ss:mm ",1);//此处最后一个参数表示从第一行开始填入数据（表头为第0行）
            workbook = exportUtils.getSXSSFWorkbook();
        } catch (FileNotFoundException e) {
            logger.error("VisitService.getExcel(ConcurrentHashMap<String, String> searchInfo) happened an FileNotFoundException,error message:"+e.getMessage());
        } catch (IOException ex) {
            logger.error("VisitService.getExcel(ConcurrentHashMap<String, String> searchInfo) happened an IOException,error message:"+ex.getMessage());
        }
        return workbook;
    }

    /**
     * @describe 获取基础拜访列表的excel文件
     * @param searchInfo 查询条件参数列表
     * @return SXSSFWorkbook
     */
    public SXSSFWorkbook getDetailExcel(ConcurrentHashMap<String, String> searchInfo){
        SXSSFWorkbook workbook = null;
        String sql="select if(ISNULL(lc.district) or lc.district='',lc.city,lc.district) as District,lc.*,DATE_FORMAT(lc.login_time,'%Y-%m-%d %H:%i:%S') as loginTime ";
        sql += "  From login_count lc where 1=1 ";
        for(Map.Entry<String, String> item : searchInfo.entrySet()){
            sql += buildQueryDetails(item);
        }
        sql += " order by lc.login_time desc";
        List<Record> seq = Db.find(sql);
        String[] excelHeaders = new String[]{"手机号","工号","用户名","地市","区县","渠道ID","渠道名称","登录时间","设备信息"};
        ExportUtils exportUtils = new ExportUtils(excelHeaders);
        String[] keys = new String[]{"mobile","login_no","user_name","city","District","channel_id","channel","loginTime","sign_device"};

        try {
            exportUtils.exportExcel(keys,seq,"yyyy-MM-dd HH:ss:mm ",1);//此处最后一个参数表示从第一行开始填入数据（表头为第0行）
            workbook = exportUtils.getSXSSFWorkbook();
        } catch (FileNotFoundException e) {
            logger.error("VisitService.getExcel(ConcurrentHashMap<String, String> searchInfo) happened an FileNotFoundException,error message:"+e.getMessage());
        } catch (IOException ex) {
            logger.error("VisitService.getExcel(ConcurrentHashMap<String, String> searchInfo) happened an IOException,error message:"+ex.getMessage());
        }
        return workbook;
    }

    private String buildQuery(Map.Entry<String, String> item){
        if("start_date".equalsIgnoreCase(item.getKey())){
            String start_time = item.getValue().replaceAll(" ", "");
            return " and lc.login_time >='" + start_time + " 00:00:00' ";
        }
        if("end_date".equalsIgnoreCase(item.getKey())){
            String end_time = item.getValue().replaceAll(" ", "");
            return " and lc.login_time <='" + end_time + " 59:59:59'";
        }
        if("org_id".equalsIgnoreCase(item.getKey())){
            String orgId = item.getValue();
            return getOrgLimit(orgId);
        }
        return " and " + item.getKey() + " like '%" + item.getValue() + "%'";
    }

    private String buildQueryDetails(Map.Entry<String, String> item){
        if("start_date".equalsIgnoreCase(item.getKey())){
            String start_time = item.getValue().replaceAll(" ", "");
            return " and DATE_FORMAT(lc.login_time,'%Y-%m-%d') >='" + start_time + "' ";
        }
        if("end_date".equalsIgnoreCase(item.getKey())){
            String end_time = item.getValue().replaceAll(" ", "");
            return " and DATE_FORMAT(lc.login_time,'%Y-%m-%d') <='" + end_time + "' ";
        }
        if("org_id".equalsIgnoreCase(item.getKey())){
            String orgId = item.getValue();
            if(orgId.equals("0")) return "";
            return getOrgLimit(orgId);
        }
        if("city_code".equalsIgnoreCase(item.getKey())){
            String city_code = item.getValue();
            return " and lc.city_code="+city_code+" and area_code=0";
        }
        return " and " + item.getKey() + " like '%" + item.getValue() + "%'";
    }

    private String getOrgLimit(String orgId){
        String sql = " and lc.org_id in("
                + InterfaceUtil.getChlidOrgIdSql(Integer.parseInt(orgId), Constant.MAX_ORG_LEVEL, true) + ") ";
        return sql;
    }
}
